﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GIMS.MODEL;

namespace KPIS.GERP.GIMS.DAL
{
    public sealed class ReceiveItemDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        ConvertNullable cn = new ConvertNullable();

        public ReceiveItemDAL()
        {

        }

        public IList<ReceiveItemInfo> GetReceiveItems(Nullable<int> Sequence, Nullable<int> ReceiveSeq, string SearchText)
        {
            string strSQL;

            DataRowCollection drc = null;
            IList<ReceiveItemInfo> infoList = new List<ReceiveItemInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@Sequence", Sequence);
                dbManager.AddParameters(2, "@ReceiveSeq", ReceiveSeq);
                dbManager.AddParameters(3, "@SearchText", SearchText);

                strSQL = "SELECT ri.*"
                            + ", idn.ITEM_DESCR, dp.ICM_DP_TYPE_NAME"
                            + ", mi.ICM_RES_ITEM_CODE, mi.ICM_RES_ITEM_NAME, js.JOB_SITE_NAME"
                        + " FROM ICM_RECEIVE_ITEM ri"
                        + " INNER JOIN ICM_RECEIVE rc ON ri.ICM_RECEIVE_SEQ = rc.ICM_RECEIVE_SEQ"
                        + " LEFT JOIN ICM_DELIVERY_NOTE dn ON rc.ICM_DN_SEQ = dn.ICM_DN_SEQ"
                        + " LEFT JOIN ICM_DELIVERY_NOTE_ITEM idn ON ri.ICM_DN_ITEM_SEQ = idn.ICM_DN_ITEM_SEQ"
                        + " LEFT JOIN ICM_MAS_RESOURCE_ITEM mi ON ri.ICM_RES_ITEM_SEQ = mi.ICM_RES_ITEM_SEQ"
                        + " LEFT JOIN ICM_MAS_DEPRECIATION_TYPE dp ON ri.ICM_DP_TYPE_SEQ = dp.ICM_DP_TYPE_SEQ"
                        + " LEFT JOIN HRM_MP_JOB_SITES js ON ri.TO_JOB_SITE_SEQ = js.JOB_SITE_SEQ"
                        + " WHERE ri.RECORD_STATUS <> @DeleteRecord"
                        + (Sequence.HasValue ? " AND ri.ICM_RC_ITEM_SEQ = @Sequence" : "")
                        + (ReceiveSeq.HasValue ? " AND ri.ICM_RECEIVE_SEQ = @ReceiveSeq" : "");

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        ReceiveItemInfo info = new ReceiveItemInfo();
                        M_IcmReceiveObject rcInfo = new M_IcmReceiveObject();
                        DeliveryNoteInfo dnInfo = new DeliveryNoteInfo();
                        DeliveryNoteItemInfo dnitemInfo = new DeliveryNoteItemInfo();
                        ResourceMasterItemInfo resInfo = new ResourceMasterItemInfo();
                        MasterDepreciationTypeInfo dpinfo = new MasterDepreciationTypeInfo();
                        HrmJobSiteInfo jsInfo = new HrmJobSiteInfo();

                        info.Seq = Convert.ToInt32(drc[i]["icm_rc_item_seq"]);
                        rcInfo.icm_receive_seq = Convert.ToInt32(drc[i]["icm_receive_seq"].ToString());
                        info.ReceiveSeq = rcInfo;
                        dnitemInfo.Seq = cn.ConvertNullInt(drc[i]["icm_dn_item_seq"]);
                        dnitemInfo.Descr = drc[i]["item_descr"].ToString();
                        info.DeliveryNoteItem = dnitemInfo;
                        resInfo.Seq = cn.ConvertNullInt(drc[i]["icm_res_item_seq"]);
                        resInfo.Code = drc[i]["icm_res_item_code"].ToString();
                        resInfo.Name = drc[i]["icm_res_item_name"].ToString();
                        info.ResourceItemSeq = resInfo;
                        dpinfo.Seq = cn.ConvertNullInt(drc[i]["icm_dp_type_seq"]);
                        dpinfo.Name = drc[i]["icm_dp_type_name"].ToString();
                        info.DepreciationType = dpinfo;
                        jsInfo.Seq = cn.ConvertNullInt(drc[i]["to_job_site_seq"]);
                        jsInfo.Name = drc[i]["job_site_name"].ToString();
                        info.JobSiteSeq = jsInfo;
                        info.LotNumber = drc[i]["lot_number"].ToString();
                        info.SerialNumber = drc[i]["serial_number"].ToString();
                        info.UpperItemSeq = cn.ConvertNullInt(drc[i]["upper_icm_rc_item_seq"]);
                        info.DateExpired = cn.ConvertNullDateTime(drc[i]["date_expired"]);
                        info.Description = drc[i]["descr"].ToString();
                        info.Accessary = drc[i]["accessary"].ToString();

                        info.Quantity = Int32.Parse(drc[i]["qty"].ToString());
                        info.UnitPrice = Convert.ToDouble(drc[i]["price"]);
                        info.Remark = drc[i]["remarks"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }
        public void Insert(ReceiveItemInfo info)
        {
            string strSQL;           

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(17);

                dbManager.AddParameters(0, "@icm_receive_seq", info.ReceiveSeq.icm_receive_seq);
                dbManager.AddParameters(1, "@icm_dn_item_seq", info.DeliveryNoteItem.Seq);
                dbManager.AddParameters(2, "@icm_res_item_seq", info.ResourceItemSeq.Seq);
                dbManager.AddParameters(3, "@to_job_site_seq", info.JobSiteSeq.Seq);
                dbManager.AddParameters(4, "@upper_icm_rc_item_seq", info.UpperItemSeq);
                dbManager.AddParameters(5, "@lot_number", info.LotNumber);
                dbManager.AddParameters(6, "@serial_number", info.SerialNumber);
                dbManager.AddParameters(7, "@date_expired", info.DateExpired);
                dbManager.AddParameters(8, "@descr", info.Description);
                dbManager.AddParameters(9, "@accessary", info.Accessary);
                dbManager.AddParameters(10, "@qty", info.Quantity);
                dbManager.AddParameters(11, "@price", info.UnitPrice);
                dbManager.AddParameters(12, "@icm_dp_type_seq", info.DepreciationType.Seq);
                dbManager.AddParameters(13, "@remark", info.Remark);
                dbManager.AddParameters(14, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(15, "@created_by", info.Created_by);
                dbManager.AddParameters(16, "@created_when", DateTime.Now);

                strSQL = " INSERT INTO ICM_RECEIVE_ITEM "
                    + " (ICM_RECEIVE_SEQ, ICM_DN_ITEM_SEQ, ICM_RES_ITEM_SEQ, TO_JOB_SITE_SEQ, UPPER_ICM_RC_ITEM_SEQ"
                        + ", LOT_NUMBER, SERIAL_NUMBER, DATE_EXPIRED, DESCR, ACCESSARY, QTY, PRICE, ICM_DP_TYPE_SEQ"
                        + ", REMARKS, RECORD_STATUS,CREATED_BY,CREATED_WHEN) "
                    + "  VALUES "
                        + " (@icm_receive_seq, @icm_dn_item_seq, @icm_res_item_seq, @to_job_site_seq, @upper_icm_rc_item_seq"
                        + ", @lot_number, @serial_number"
                        + ", @date_expired, @descr, @accessary, @qty, @price, @icm_dp_type_seq"
                        + ", @remark, @record_status, @created_by, @created_when) ";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public string InsertReturnSerialNo(ReceiveItemInfo info)
        {
            string strSQL;
            string lastNumber = "";
            int lastSeq = 0;
            DataSet ds;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(17);

                dbManager.AddParameters(0, "@icm_receive_seq", info.ReceiveSeq.icm_receive_seq);
                dbManager.AddParameters(1, "@icm_dn_item_seq", info.DeliveryNoteItem.Seq);
                dbManager.AddParameters(2, "@icm_res_item_seq", info.ResourceItemSeq.Seq);
                dbManager.AddParameters(3, "@to_job_site_seq", info.JobSiteSeq.Seq);
                dbManager.AddParameters(4, "@upper_icm_rc_item_seq", info.UpperItemSeq);
                dbManager.AddParameters(5, "@lot_number", info.LotNumber);
                dbManager.AddParameters(6, "@serial_number", info.SerialNumber);
                dbManager.AddParameters(7, "@date_expired", info.DateExpired);
                dbManager.AddParameters(8, "@descr", info.Description);
                dbManager.AddParameters(9, "@accessary", info.Accessary);
                dbManager.AddParameters(10, "@qty", info.Quantity);
                dbManager.AddParameters(11, "@price", info.UnitPrice);
                dbManager.AddParameters(12, "@icm_dp_type_seq", info.DepreciationType.Seq);
                dbManager.AddParameters(13, "@remark", info.Remark);
                dbManager.AddParameters(14, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(15, "@created_by", info.Created_by);
                dbManager.AddParameters(16, "@created_when", DateTime.Now);

                strSQL = " INSERT INTO ICM_RECEIVE_ITEM "
                    + " (ICM_RECEIVE_SEQ, ICM_DN_ITEM_SEQ, ICM_RES_ITEM_SEQ, TO_JOB_SITE_SEQ, UPPER_ICM_RC_ITEM_SEQ"
                        + ", LOT_NUMBER, SERIAL_NUMBER, DATE_EXPIRED, DESCR, ACCESSARY, QTY, PRICE, ICM_DP_TYPE_SEQ"
                        + ", REMARKS, RECORD_STATUS,CREATED_BY,CREATED_WHEN) "
                    + "  VALUES "
                        + " (@icm_receive_seq, @icm_dn_item_seq, @icm_res_item_seq, @to_job_site_seq, @upper_icm_rc_item_seq, @lot_number"
                        + ", CONCAT('INV-', year(@created_when), month(@created_when), '-'"
                            + ", (select right("
                                + " concat('0000'"
                                    + ", coalesce("
                                        + "right("
                                            + "(select max(sub.serial_number)"
                                            + " from icm_receive_item sub"
                                            + " where month(sub.created_when) = month(@created_when)"
                                                + " and year(sub.created_when) = year(@created_when)"
                                            + ")"
                                        + ", 4)"
                                    + ", 0)"
                                + " + 1)"
                            + ",4)"
                        + "))"
                        + ", @date_expired, @descr, @accessary, @qty, @price, @icm_dp_type_seq"
                        + ", @remark, @record_status, @created_by, @created_when) ";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

                strSQL = "SELECT LAST_INSERT_ID()"
                    + " FROM ICM_RECEIVE_ITEM";
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    lastSeq = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                }

                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@lastSeq", lastSeq);

                strSQL = "SELECT SERIAL_NUMBER"
                    + " FROM ICM_RECEIVE_ITEM"
                    + " WHERE ICM_RC_ITEM_SEQ = @lastSeq";
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    lastNumber = ds.Tables[0].Rows[0][0].ToString();
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return lastNumber;
        }
        public void Update(ReceiveItemInfo info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(17);

                dbManager.AddParameters(0, "@icm_rc_item_seq", info.Seq);
                dbManager.AddParameters(1, "@icm_dn_item_seq", info.DeliveryNoteItem.Seq);
                dbManager.AddParameters(2, "@icm_res_item_seq", info.ResourceItemSeq.Seq);
                dbManager.AddParameters(3, "@to_job_site_seq", info.JobSiteSeq.Seq);
                dbManager.AddParameters(4, "@upper_icm_rc_item_seq", info.UpperItemSeq);
                dbManager.AddParameters(5, "@lot_number", info.LotNumber);
                dbManager.AddParameters(6, "@serial_number", info.SerialNumber);
                dbManager.AddParameters(7, "@date_expired", info.DateExpired);
                dbManager.AddParameters(8, "@descr", info.Description);
                dbManager.AddParameters(9, "@accessary", info.Accessary);
                dbManager.AddParameters(10, "@qty", info.Quantity);
                dbManager.AddParameters(11, "@price", info.UnitPrice);
                dbManager.AddParameters(12, "@icm_dp_type_seq", info.DepreciationType.Seq);
                dbManager.AddParameters(13, "@remark", info.Remark);
                dbManager.AddParameters(14, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(15, "@updated_by", info.Updated_by);
                dbManager.AddParameters(16, "@updated_when", DateTime.Now);

                strSQL = " UPDATE ICM_RECEIVE_ITEM "
                        + " SET ICM_DN_ITEM_SEQ = @icm_dn_item_seq"
                        + ", ICM_RES_ITEM_SEQ = @icm_res_item_seq"
                        + ", TO_JOB_SITE_SEQ = @to_job_site_seq"
                        + ", UPPER_ICM_RC_ITEM_SEQ = @upper_icm_rc_item_seq"
                        + ", LOT_NUMBER = @lot_number"
                        + ", SERIAL_NUMBER = @serial_number"
                        + ", DATE_EXPIRED = @date_expired"
                        + ", DESCR = @descr"
                        + ", ACCESSARY = @accessary"
                        + ", QTY = @qty"
                        + ", PRICE = @price"
                        + ", ICM_DP_TYPE_SEQ = @icm_dp_type_seq"
                        + ", REMARKS = @remark"
                        + ", RECORD_STATUS = @record_status"
                        + ", UPDATED_BY = @updated_by"
                        + ", UPDATED_WHEN = @updated_when"
                        + " WHERE ICM_RC_ITEM_SEQ = @icm_rc_item_seq ";
 
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void Delete(int Sequence, int UpdatedBy)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@icm_rc_item_seq", Sequence);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", UpdatedBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Now);

                strSQL = " UPDATE ICM_RECEIVE_ITEM "
                        + " SET RECORD_STATUS = @record_status"
                        + ", UPDATED_BY = @updated_by"
                        + ", UPDATED_WHEN = @updated_when"
                        + " WHERE ICM_RC_ITEM_SEQ = @icm_rc_item_seq ";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void DeleteAll(IList<ReceiveItemInfo> infoList)
        {
            string strSQL;
            string seq = "";
            for (int i = 0; i < infoList.Count; i++)
            {
                if (i == 0)
                {
                    seq += infoList[i].Seq;
                }
                else
                {
                    seq += "," + infoList[i].Seq;
                }
            }

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@updated_by", infoList[0].Updated_by);
                dbManager.AddParameters(2, "@updated_when", DateTime.Now);

                strSQL = "UPDATE ICM_RECEIVE_ITEM"
                    + " SET RECORD_STATUS = @record_status"
                    + ", UPDATED_BY = @updated_by"
                    + ", UPDATED_WHEN = @updated_when"
                    + " WHERE ICM_RC_ITEM_SEQ IN (" + seq + ")";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
